package com.service;

import com.dao.ExperimentDao;
import com.dao.SystemDao;
import com.dao.UserDao;
import com.entity.Experiment;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.util.FileUtil;
import com.util.LocalInformation;
import com.util.excelUtil;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.sql.SQLIntegrityConstraintViolationException;
import java.util.*;

/**
 * Created by wlc on 2018/4/12.
 */
public class ExperimentOperator {
    /**
     * 将Experiment表单数据处理后存储到数据库
     * @param part1
     * @param part2
     * @param part3
     * @param part4
     * @param file_names
     */
    public static boolean saveExperimentForm(String part1,String part2,String part3,String part4,String[] file_names){
        Experiment experiment = new Experiment();
        int[] time= LocalInformation.getNowDate(experiment.getDate());

//        part1
        experiment.setExperiment_User_AND_Method(part1);
        String file_path = "/file_data/experiment_part/EXPERIMENT/"+time[0] + "_"+time[1]+"_"+time[2]+"/"+experiment.getExperiment_User_AND_Method().get(0)+"_";

//        part2
        experiment.setExperiment_RawMaterial(part2);

//        part3
        String sem_path = file_names[0].equals("null")?"null":file_path+file_names[0];
        String stm_path = file_names[1].equals("null")?"null":file_path+file_names[1];
        experiment.setExperiment_Characterization(part3,sem_path,stm_path);

//        part4
        experiment.setExperiment_Biological_evaluation(part4);

//        存储到数据库
        return ExperimentDao.data_save(experiment);
    }

    /**
     * 获取数据库中所有的模板实验原料名称
     * @return
     * @throws Exception
     */
    public static String getExperimentMaterial() throws Exception{
        HashMap<String,String> experimentMaterial = ExperimentDao.materialAcquisition();

        ObjectMapper mapper = new ObjectMapper();
        return mapper.writeValueAsString(experimentMaterial);
    }

    /**
     * 获取指定模板实验原料内容
     * @param tableId
     * @param index
     * @return
     */
    public static String getExperimentMaterialContent(String tableId,String index){
        ArrayList<String> rowContent = ExperimentDao.materialContentAcquisition(tableId,index);

        return StringUtils.join(rowContent,"wlcniubi");
    }

    /**
     * 计算实验数据搜索结果并存储到磁盘，并返回需要显示内容
     * @param user_cookie_value
     * @param projectPath
     * @param base
     * @param keyword
     * @return
     * @throws IOException
     */
    public static String setSearchResult(String user_cookie_value, String projectPath, String base, String keyword) throws IOException {
        String user_file_Path = projectPath+"/file_data/user/"+user_cookie_value.split("wlcniubi")[0]+"/";

        ArrayList<ArrayList<String>> search_result = ExperimentDao.searchExperiment_data_by_keyword(base, keyword);
        UserDao.saveUserSearchResult(search_result,user_file_Path+"experiment_searchResult");

        String last_result = "";

        if(search_result.size() > 0) {
            ArrayList<String> process_result = new ArrayList<>();
            for(ArrayList<String> row_search_result: search_result) {
                String[] show_content = {row_search_result.get(0),row_search_result.get(1) ,row_search_result.get(2), row_search_result.get(3)};
                process_result.add(StringUtils.join(show_content,"lxyniubi"));
            }
            last_result = StringUtils.join(process_result.toArray(),"wlcniubi");
        }
        return last_result;
    }

    /**
     * 获取选择后的搜索结果
     * @param user_cookie_value
     * @param projectPath
     * @param select_data_id
     * @return
     * @throws IOException
     */
    public static String getSearchResult(String user_cookie_value, String projectPath, String select_data_id) throws IOException {

        String user_file_Path = projectPath+"/file_data/user/"+user_cookie_value.split("wlcniubi")[0]+"/";
        ArrayList<ArrayList<String>> search_result = UserDao.getUserSearchResult(user_file_Path+"experiment_searchResult");
        ArrayList<ArrayList<String>> select_result = new ArrayList<>();

        for(ArrayList<String> result: search_result) {
            if(select_data_id.contains(result.get(0))) {
                select_result.add(result);
            }
        }
        ArrayList<String> select_result_str = new ArrayList<>();
        for(ArrayList<String> row_select_result: select_result) {
            select_result_str.add(StringUtils.join(row_select_result.toArray(),"lxyniubi"));
        }
        return StringUtils.join(select_result_str.toArray(),"wlcniubi");
    }

    /**
     * 解析上传excel表单
     * @param timeId
     * @param path
     * @param file_name
     * @param user
     * @param date
     */
    public static boolean Analysis_experiment_table(String timeId, String path, String file_name, String user, String date) {
        ArrayList<String> experiment_User_AND_Method = new ArrayList<String>(){{
            add(timeId);
            add(user);
            add(date);
        }};
        ArrayList<String> experiment_RawMaterial = new ArrayList<String>(){{ add(timeId); }};
        ArrayList<String> experiment_Characterization = new ArrayList<String>(){{ add(timeId); }};
        ArrayList<String> experiment_Biological_evaluation = new ArrayList<String>(){{ add(timeId); }};

        String abs_path = path + file_name;

        try {
            InputStream in = new FileInputStream(new File(abs_path));
            XSSFWorkbook wb = new XSSFWorkbook(in);
            XSSFSheet sheet = wb.getSheetAt(0);
            XSSFRow row = sheet.getRow(1);
            XSSFCell cell = null;

            //experiment_User_AND_Method
            cell = row.getCell(0);
            String doi = excelUtil.getCellStrValue(cell);
            experiment_User_AND_Method.add(doi);
            row = sheet.getRow(27);
            for(int i = 0; i <= 8; i++) {
                if(i == 0) {
                    experiment_User_AND_Method.add(readMulCol(sheet, 27, i, 1, "备选填法："));
                } else {
                    cell = row.getCell(i);
                    experiment_User_AND_Method.add(excelUtil.getCellStrValue(cell));
                }
            }
            //experiment_RawMaterial
            row = sheet.getRow(1);
            for(int i = 1; i <= 70; i++) {   //排除DOI
                cell = row.getCell(i);
                experiment_RawMaterial.add(excelUtil.getCellStrValue(cell));
            }

            //experiment_Characterization
            row = sheet.getRow(27);
            Map<String, PictureData> index_pic = excelUtil.getSheetPictures(sheet);      //get pic from excel
            Map<String, String> name_path = saveExcelPic(abs_path, index_pic);

            Map<Integer, String> pic_col = new HashMap<Integer, String>() {
                {
                    put(10, "XRD");
                    put(17, "SEM");
                    put(18, "TEM");
                }
            };
            for(int i =9; i <= 13; i++) {
                if(pic_col.keySet().contains(i)) {
                    String filePath = name_path.get(pic_col.get(i));
                    experiment_Characterization.add(filePath);
                } else {
                    cell = row.getCell(i);
                    String temp = excelUtil.getCellStrValue(cell);
                    if(i == 13 && temp.equals("null")) temp = "unknown";
                    experiment_Characterization.add(temp);
                }
            }
            for(int i = 26; i <= 43; i++) {          //26 - 43
                cell = row.getCell(i);
                String temp = excelUtil.getCellStrValue(cell);
                experiment_Characterization.add(temp);
            }
            for(int i = 14; i <= 18; i++) {
                if(pic_col.keySet().contains(i)) {
                    String filePath = name_path.get(pic_col.get(i));
                    experiment_Characterization.add(filePath);
                } else {
                    cell = row.getCell(i);
                    String temp = excelUtil.getCellStrValue(cell);
                    experiment_Characterization.add(temp);
                }
            }

            row = sheet.getRow(29);
            for(int i = 19; i <= 24; i++) {            //形状
                cell = row.getCell(i);
                String temp = excelUtil.getCellStrValue(cell);
                experiment_Characterization.add(temp);
            }

            //experiment_Biological_evaluation
            row = sheet.getRow(47);
            for(int i = 0; i <= 18; i++) {
                if(i!=5 && i!=6 && i!=7 && i!=8) {
                    cell = row.getCell(i);
                    String temp;
                    temp = excelUtil.getCellStrValue(cell);
                    if(i == 3 && temp.equals("null")) temp = "unknown";
                    experiment_Biological_evaluation.add(temp);
                }
            }
            //细胞抑制率
            String inhibition_rate = readMulCol(sheet,47, 5, 3, "时间（天）");
            experiment_Biological_evaluation.add(6, inhibition_rate);

            //抗肿瘤
            row = sheet.getRow(67);
            for(int i = 0; i <= 10; i++) {
                if(i==5 || i==6 || i==7) {
                    continue;
                }
                if(i == 2 || i == 3) {
                    experiment_Biological_evaluation.add(readMulCol(sheet, 67, i, 1, "备选填法："));
                } else {
                    cell = row.getCell(i);
                    experiment_Biological_evaluation.add(excelUtil.getCellStrValue(cell));
                }
            }
            //
//            System.out.println(readMulCol(sheet, 67, 5, 3, "时间（天）"));
//            System.out.println("---------------------");
//            System.out.println(readMulCol(sheet, 67, 12, 3, "时间（天）"));
//            System.out.println("---------------------");
//            System.out.println(readMulCol(sheet, 67, 16, 3, "时间（天）"));
            //

            experiment_Biological_evaluation.add(readMulCol(sheet, 67, 5, 3, "时间（天）"));   //癌细胞抑制率
            experiment_Biological_evaluation.add(readMulCol(sheet, 67, 12, 3, "时间（天）"));     //细胞内钙
            experiment_Biological_evaluation.add(readMulCol(sheet, 67, 16, 3, "时间（天）"));     //ROS量
            for(int i = 20; i <= 30; i++) {
                String temp = null;
                if(i == 28) {
                    temp = readMulCol(sheet, 67, 28, 1, "备选填法：");
                } else {
                    cell = row.getCell(i);
                    temp = excelUtil.getCellStrValue(cell);
                }
                experiment_Biological_evaluation.add(temp);
            }
            experiment_Biological_evaluation.add(readMulCol(sheet, 67, 31, 3, "时间（天）"));     //缩小体积
//            System.out.println(experiment_Biological_evaluation);
//            System.out.println(experiment_Biological_evaluation.size());

            in.close();

        } catch (IOException e) {
            e.printStackTrace();
        }
        Experiment experiment_excel = new Experiment(experiment_User_AND_Method, experiment_RawMaterial, experiment_Characterization, experiment_Biological_evaluation);
        return ExperimentDao.data_save(experiment_excel);
    }


    /**
     * save pic
     * @param path
     * @param index_pic
     * @throws IOException
     */
    private static Map<String, String> saveExcelPic(String path, Map<String, PictureData> index_pic) throws IOException {
        Map<String, String> name_path = new HashMap<>();
        Set<String> index = new HashSet<String>() {
            {
                add("27_10");
                add("27_17");
                add("27_18");
            }
        };
        for(Map.Entry<String, PictureData> entry: index_pic.entrySet()) {
            String picIndex = entry.getKey();
            if(index.contains(picIndex)) {
                String picName = null;
                switch (picIndex) {
                    case "27_10" :picName = "XRD";break;
                    case "27_17" :picName = "SEM";break;
                    case "27_18" :picName = "TEM";break;
                }
                PictureData pic = entry.getValue();
                String ext = pic.suggestFileExtension();

                String final_path = path + picName + "." + ext;
                name_path.put(picName, final_path);
                byte[] data = pic.getData();
                FileOutputStream out = new FileOutputStream(new File(final_path));
                out.write(data);
                out.close();
            }
        }
        return name_path;
    }

    /**
     *
     * @param rowInx
     * @param colInx
     * @param sheet
     * @param colNum
     * @return
     */
    private static String readMulCol(XSSFSheet sheet, int rowInx, int colInx, int colNum, String annotation) {
        List<String> res = new ArrayList<>();
        XSSFRow row = sheet.getRow(rowInx);
        XSSFCell cell = row.getCell(colInx);
        int j = 1;
        while(cell != null && !excelUtil.getCellStrValue(cell).equals("null") && !excelUtil.getCellStrValue(cell).equals(annotation)) {
            int i = 0;
            List<String> temp = new ArrayList<>();         //存储每行数据

            while(i != colNum) {
                cell = row.getCell(colInx + i);
                //
//                System.out.println("*******************: " + excelUtil.getCellStrValue(cell));
                temp.add(excelUtil.getCellStrValue(cell));
                i++;
            }
            res.add(StringUtils.join(temp, ","));
            if(sheet.getRow(rowInx + j) != null) {
                row = sheet.getRow(rowInx + j);
            } else {
                break;
            }
            if(row.getCell(colInx) == null) {
                break;
            } else {
                cell = row.getCell(colInx);
            }
            j++;
        }
        String final_value = StringUtils.join(res, ";");
        if(final_value.equals("")) {
            final_value = "null";
        }
        return final_value;
    }


}

